Skip to main content

Aggregates and Functions

Stateless Functions​

Stateless Functions work with fields of one specific class/field.

Functions for Numerics​

ExpressionExplanation
max(x,y)Maximum
min(x,y)Minimum
abs(x)Absolute value
sqrt(x)Square root
log(x)Natural logarithm (base e).
exp(x)Euler's number e raised to the power of a value
floor(x)Largest value that is less than or equal to the argument and is equal to a mathematical integer.
ceil(x)Smallest value that is greater than or equal to the argument and is equal to a mathematical integer.
#Shell format

==> select max(1,23)

>_,TIMESTAMP,SYMBOL,TYPE,"MAX (1, 23)"
0,_,,CUSTOM,23

==> select abs(-10)

>_,TIMESTAMP,SYMBOL,TYPE,ABS (-10)
0,_,,CUSTOM,10

==> select float32(10.1123456789)

>_,TIMESTAMP,SYMBOL,TYPE,FLOAT32 (101123456789E-10)
0,_,,CUSTOM,10.112346

Functions for VARCHAR​

Functions for VARCHAR datatype.

ExpressionExplanation
length(s)string length
uppercase(s)uppercase string
lowercase(s)lowercase string
reverse(s)reverse string
indexof(x, y)find y in x, return index
substr(x, start, end)substring
#Shell format

==> select length('hello')

>_,TIMESTAMP,SYMBOL,TYPE,LENGTH (hello)
0,_,,CUSTOM,5

==> select uppercase('hello')

>_,TIMESTAMP,SYMBOL,TYPE,UPPERCASE (hello)
0,_,,CUSTOM,HELLO 

==> select lowercase('HeLlo')

>_,TIMESTAMP,SYMBOL,TYPE,LOWERCASE (HeLlo)
0,_,,CUSTOM,hello 

==> select reversed('hello')

>_,TIMESTAMP,SYMBOL,TYPE,REVERSED (hello)
0,_,,CUSTOM,olleh

==> select indexof('h', 'hello')

>_,TIMESTAMP,SYMBOL,TYPE,"INDEXOF (h, hello)"
0,_,,CUSTOM,-1 

==> select substr('Hello, World!', 7, 14)

>_,TIMESTAMP,SYMBOL,TYPE,"SUBSTR (Hello, World!, 7, 14)"
0,_,,CUSTOM,World!
SELECT length(entry.exchangeid) AS length
FROM bittrex
ARRAY JOIN entries IN entry

Functions for Arrays​

ExpressionExplanation
empty(arr)is array empty
notempty(arr)is array not empty
size(arr)array size
max(arr)array maximum
min(arr)array minimum
mean(arr)array mean
sum(arr)array sum
enumerate(arr)array indices
sort(arr)array sort
indexof(arr, el)find index of element
any(arr)if any element is true
all(arr)if all elements are true
Examples with functions for arrays
SELECT
sum(entries.price + entries.size) AS SUM
FROM bittrex

SELECT
avg(entries.price) AS AVG
FROM bittrex

SELECT sort(entries.price) FROM bittrex

SELECT size(entries.price) FROM bittrex

SELECT 
ANY(entries.price > 200)
FROM bittrex

SELECT * FROM bitfinex WHERE notEmpty(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])

Internal Functions​

ExpressionExplanation
streams()returns an array of streams with full schema information
symbols(stream key)returns an array of symbols in stream
spaces(stream key)returns an array of spaces in stream
stateless_functions()returns an array of stateless functions supported by QQL
stateful_functions()returns an array of stateful functions supported by QQL
Examples with internal functions
# Select all streams
SELECT s.key
ARRAY JOIN streams() AS s

# Select all symbols from secruties stream
SELECT s
ARRAY JOIN symbols('securities') AS s

# Select all stateless functions with arguments and types
SELECT f.id, f.arguments.name, f.arguments.dataType.baseName
ARRAY JOIN STATELESS_FUNCTIONS() as f

Stateful Functions​

Keywords​

Time template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER/RESET] OVER [EVERY] TIME(5m)

Count template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER] OVER COUNT(100)
  • RUNNING - the result is returned for every input message. For example we compute running max, it means, that for each message we receive max for this message and all previous messages in a group.
  • OVER [EVERY] TIME(5m) - computes functions separately for each (5 minutes) interval and delivers updates at the end of every (5 minutes) interval. If we add EVERY, we expect empty result for empty 5m intervals when no messages were published.
  • TRIGGER OVER [EVERY] TIME(5m) - receive function results every 5 minutes and function is computed over the entire stream. If we add EVERY, we expect update for empty 5m intervals when no messages were published.
  • OVER COUNT(100) - compute function separately for each 100 messages group and receive update every 100 messages.
  • TRIGGER OVER COUNT(100) - compute function over the entire stream, but receive updates every 100 messages.
  • RESET - is used to reset function based on provided conditions. For example, reset running calculation for each time period instead of carrying on with the cumulative counting.

Functions Syntax​

function{initArg1: value1, initArg2: value2, ...}(argValue1, argValue2)

OR

function{value1, value2, ...}(argValue1, argValue2, ...)

Init arguments could be passed to function like named args and also like position arguments in {...} braces. Init arguments are constant, so you cannot pass here selectors or anything similar. Arguments are passed to function in (...) parentheses as positional arguments.

Examples​

messagemax(field)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampmax
1
2
3
4
5
6
7
8
9
10
11
12
132021-03-19T08:07:41.1044737
messagemax(field) over every time(1m)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
timestampfield
2021-03-19T08:07:32623
2021-03-19T08:07:333467
2021-03-19T08:07:342374
2021-03-19T08:07:35347
2021-03-19T08:07:36NULL
2021-03-19T08:07:37-347
2021-03-19T08:07:384737
2021-03-19T08:07:39NULL
2021-03-19T08:07:40NULL
2021-03-19T08:07:41NULL
2021-03-19T08:07:427
#max price for the entire stream
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex

#max for every hour time period
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (1h)

#max from N prior messages for each message*/
SELECT
RUNNING 
max{} (max(entries.price)) AS MAX 
FROM bittrex

#incremental update for every message and MAX for the time interval
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (10m)

#max value from prior 10 messages for each interval
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance2
OVER COUNT (10)

#max from N prior messages for every interval
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance2
TRIGGER OVER COUNT (10)

#every 10 min returns max for prior N messages
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
TRIGGER OVER TIME (10m)

#returns max for every message, resets every 30 min but does not return a snapshot
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
RESET OVER TIME (30m)

#incremental update for every message and a snapshot for 10 messages
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM binance2
OVER COUNT (10)

#select BID and ASK price FROM L2EntryNew
#cast entries array to L2EntryNew type
#return max for every selection every 1 min
#filter by packageType and symbol
WITH (entries AS array(deltix.timebase.api.messages.universal.L2EntryNew)) AS 'entries',
entries[side == BID].price AS 'bidPrices',
entries[side == ASK].price AS 'askPrices'
SELECT
entries,
max{}(max(askPrices)) AS 'highAsk',
max{}(max(bidPrices)) AS 'highBid',
min{}(min(askPrices)) AS 'lowAsk',
min{}(min(bidPrices)) AS 'lowBid'
FROM bitfinex
OVER TIME(1m)
WHERE packageType == PERIODICAL_SNAPSHOT
AND symbol == 'BTCUSD'

#one-minute bars based on trades 
WITH entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
FROM bitfinex
OVER TIME(1m)
WHERE symbol == 'BTCUSD'
AND size(entries) > 0

#different price indicators 
WITH entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry].price AS 'prices'
SELECT
sma{timePeriod: 1h}(price) AS 'sma',
(bollinger{timeWindow: 1h}(price) AS 'bollinger').*,
cma{}(price) AS 'cma'
FROM bitfinex
ARRAY JOIN prices AS 'price'
OVER TIME(10m)
WHERE symbol == 'BTCUSD'
AND size(entries) > 0

#simple moving average for Prices for 1 min time period returned every 1 hour
SELECT
sma{timePeriod: 1m}(max(entries.price))
FROM bittrex
TRIGGER OVER TIME(1h)

# Volume-Weighted Average Price (VWAP)
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
OVER TIME (1m)
WHERE symbol == 'BTCUSD'

# VWAP cumulative
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
TRIGGER OVER TIME (1m)
WHERE symbol == 'BTCUSD'

# counts the number of unique FX instruments in the securities stream
SELECT size(collect_unique{}(symbol)) FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX

# builds L2 order book with 10 levels size for bittrex BTC/USDT and returns snapshots every 10 seconds
SELECT orderbook{maxDepth: 10}(this.packageType, this.entries)
FROM bittrex
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

# builds L2 order book and returns snapshots in Universal format
WITH
orderbook{maxDepth: 10}(this.packageType, this.entries) as book
SELECT
book as entries, PERIODICAL_SNAPSHOT as packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM BITFINEX
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

List of Functions​

IDInit argsArgsReturnsDescription
COUNTINT64counts messages
MAXBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes maximum value
MAXtimePeriod: INT64FLOAT64?FLOAT64?computes maximum over time window with given timePeriod
MAXperiod: INT64FLOAT64?FLOAT64?computes maximum over count window with given period
MINBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes minimum value
MINtimePeriod: INT64FLOAT64?FLOAT64?computes minimum over time window with given timePeriod
MINperiod: INT64FLOAT64?FLOAT64?computes minimum over count window with given period
SUMINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes sum of given values
SUMFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes sum of given values
SUMtimePeriod: INT64DECIMAL64?DECIMAL64?computes sum of values in time window with given time period
SUMperiod: INT32DECIMAL64?DECIMAL64?computes sum of values in count window with given period
AVGINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes avg of given values
AVGFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes avg of given values
SMAtimePeriod: INT64FLOAT64?FLOAT64?computes moving average over time window with given timePeriod
SMAperiod: INT64FLOAT64?FLOAT64?computes moving average over count window with given period
CMAFLOAT64?FLOAT64?computes cumulative moving average
EMAperiod: INT32FLOAT64?FLOAT64?computes exponential moving average with given period
EMAfactor: FLOAT64FLOAT64?FLOAT64?computes exponential moving average with given factor
ADXRperiod: INT64open, high, low, close, volume (FLOAT64)ADXRMessagecomputes Average Directional Movement Rating indicator (read more)
ATRperiod: INT64open, high, low, close, volume (FLOAT64)FLOAT64computes Average True Range (read more)
BOLLINGERpointWindow: INT64?, factor: FLOAT64FLOAT64BollingerMessagecomputes Bollinger Bands (read more)
KAMAperiod: INT64FLOAT64FLOAT64computes Kaufman's Adaptive Moving Average (read more)
LSMApointWindow: INT64 or timeWindow: INT64, useDateTime: BOOLEANFLOAT64LSMAMessagecomputes Least Squares Moving Average (read more)
MMAperiod: INT64FLOAT64FLOAT64computes Modified Moving Average (read more)
COLLECT_UNIQUEVARCHAR?ARRAYS OF VARCHARScollects and returns an array of unique strings
lastNotNullBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?fills null gaps with previous not null value
windowperiod: INT64 or timePeriod: INT64FLOAT64?ARRAY OF FLOAT64?builds fixed size (if period is set) or time (if timePeriod is set) window and returns it as an array
orderBookmaxDepth: INT32ENUM(PackageType), ARRAY of OBJECT(BaseEntry)ARRAY of OBJECT(BaseEntry)builds L2 order book
statWindowperiod: INT64 or timePeriod: INT64FLOAT64?OBJECT(StatWindowMessage), where StatWindowMessage contains: sum, count, sumOfSquares, sumOfAbs, geometricMean, harmonicMean, firstRawMoment, secondRawMoment, thirdRawMoment, forthRawMoment, variance, standardDeviation, median, min, maxcalculates statistics over fixed size (if period is set) window or time window (if timePeriod is set).